﻿using System;
using System.Collections.Generic;
using System.Data;
using BLToolkit.Data;
using PMIS.BLL.System;

namespace PMIS.BLL
{
	/// <summary>
	/// 所有业务逻辑类的基类
	/// </summary>
	public class BL_Base
	{
		#region 分页查询
		/// <summary>
		/// 分页查询
		/// </summary>
		/// <param name="strSQL">查询SQL</param>
		/// <param name="pp">分页参数</param>
		/// <returns></returns>
		protected DataTable PageQuery(string strSQL, ref PagingParameter pp)
		{
			return PageQuery(strSQL, null, ref pp);
		}
		/// <summary>
		/// 分页查询
		/// </summary>
		/// <param name="strSQL">查询SQL</param>
		/// <param name="Params">查询参数</param>
		/// <param name="pp">分页参数</param>
		/// <returns></returns>
		protected DataTable PageQuery(string strSQL, List<IDbDataParameter> Params, ref PagingParameter pp)
		{
			if (string.IsNullOrEmpty(strSQL) || pp == null)
				return null;

			DataTable result = null;
			DbManager dm = null;
			try
			{
				//	创建数据库实例
				dm = BL_DatabaseConnection.CreateConnection();

				//	查询
				result = PageQuery(strSQL, Params, ref pp, dm);
			}
			catch (Exception ex)
			{
				BL_ExceptionOperation.WriteException(ex);
				result = null;
			}
			finally
			{
				if (dm != null)
				{
					dm.Close();
					dm.Dispose();
					dm = null;
				}
			}

			return result;
		}
		/// <summary>
		/// 分页查询
		/// </summary>
		/// <param name="strSQL">查询SQL</param>
		/// <param name="Params">查询参数</param>
		/// <param name="pp">分页参数</param>
		/// <param name="dm">数据库操作对象</param>
		/// <returns></returns>
		protected DataTable PageQuery(string strSQL, List<IDbDataParameter> Params, ref PagingParameter pp, DbManager dm)
		{
			if (string.IsNullOrEmpty(strSQL) || pp == null || dm == null)
				return null;

			DataTable result = null;
			try
			{
				//	构造分页SQL
				string strPagedSQL = GeneralPagedSQL(strSQL, pp);
				//	构造总记录数SQL
				string strCountSQL = string.Format("Select Count(0) From ({0}) _TMP", strSQL);

				//	查询结果
				result = Params == null ?
					dm.SetCommand(strPagedSQL).ExecuteDataTable() :
					dm.SetCommand(strPagedSQL, Params.ToArray()).ExecuteDataTable();

				//	总记录数
				pp.TotalRecordCount = Params == null ?
					dm.SetCommand(strCountSQL).ExecuteScalar<int>() :
					dm.SetCommand(strCountSQL, Params.ToArray()).ExecuteScalar<int>();
			}
			catch (Exception ex)
			{
				BL_ExceptionOperation.WriteException(ex);
				result = null;
			}

			return result;
		}
		/// <summary>
		/// 分页查询
		/// </summary>
		/// <typeparam name="T">返回值类型</typeparam>
		/// <param name="strSQL">查询SQL</param>
		/// <param name="pp">分页参数</param>
		/// <returns></returns>
		protected List<T> PageQuery<T>(string strSQL, ref PagingParameter pp)
		{
			return PageQuery<T>(strSQL, null, ref pp);
		}
		/// <summary>
		/// 分页查询
		/// </summary>
		/// <typeparam name="T">返回值类型</typeparam>
		/// <param name="strSQL">查询SQL</param>
		/// <param name="Params">查询参数</param>
		/// <param name="pp">分页参数</param>
		/// <returns></returns>
		protected List<T> PageQuery<T>(string strSQL, List<IDbDataParameter> Params, ref PagingParameter pp)
		{
			if (string.IsNullOrEmpty(strSQL) || pp == null)
				return null;

			List<T> result = null;
			DbManager dm = null;
			try
			{
				//	创建数据库实例
				dm = BL_DatabaseConnection.CreateConnection();

				//	查询
				result = PageQuery<T>(strSQL, Params, ref pp, dm);
			}
			catch (Exception ex)
			{
				BL_ExceptionOperation.WriteException(ex);
				result = null;
			}
			finally
			{
				if (dm != null)
				{
					dm.Close();
					dm.Dispose();
					dm = null;
				}
			}

			return result;
		}
		/// <summary>
		/// 分页查询
		/// </summary>
		/// <typeparam name="T">返回值类型</typeparam>
		/// <param name="strSQL">查询SQL</param>
		/// <param name="Params">查询参数</param>
		/// <param name="pp">分页参数</param>
		/// <param name="dm">数据库操作对象</param>
		/// <returns></returns>
		protected List<T> PageQuery<T>(string strSQL, List<IDbDataParameter> Params, ref PagingParameter pp, DbManager dm)
		{
			if (string.IsNullOrEmpty(strSQL) || pp == null || dm == null)
				return null;

			List<T> result = null;
			try
			{
				//	构造分页SQL
				string strPagedSQL = GeneralPagedSQL(strSQL, pp);
				//	构造总记录数SQL
				string strCountSQL = string.Format("Select Count(0) From ({0}) _TMP", strSQL);

				//	查询结果
				result = Params == null ?
					dm.SetCommand(strPagedSQL).ExecuteList<T>() :
					dm.SetCommand(strPagedSQL, Params.ToArray()).ExecuteList<T>();

				//	总记录数
				pp.TotalRecordCount = Params == null ?
					dm.SetCommand(strCountSQL).ExecuteScalar<int>() :
					dm.SetCommand(strCountSQL, Params.ToArray()).ExecuteScalar<int>();
			}
			catch (Exception ex)
			{
				BL_ExceptionOperation.WriteException(ex);
				result = null;
			}

			return result;
		}
		/// <summary>
		/// 根据参数构造分页SQL
		/// </summary>
		/// <param name="strSQL">SQL语句</param>
		/// <param name="pp">分页参数</param>
		/// <returns></returns>
		protected string GeneralPagedSQL(string strSQL, PagingParameter pp)
		{
			if (string.IsNullOrEmpty(strSQL) || pp == null)
				return string.Empty;

			//	pp.CurrentPageIndex == 0 就不分页
			return pp.CurrentPageIndex == 0 ? strSQL : string.Format("Select _tmp.* From ({0}) _tmp Limit {1},{2}", strSQL, (pp.CurrentPageIndex - 1) * pp.PageSize, pp.PageSize);
		}
		#endregion
	}
}
